﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Nhibernate;
using Bussiness;
using NHibernate.Criterion;
using NHibernate;
using Bussiness;
using System.Data;
using NHibernate.Mapping;
using System.Collections;
using System.ComponentModel;
using System.Data.SqlClient;
using NHibernate.Transform;


namespace Bussiness.Service
{
    public class Product_Service
    {

        public List<CateloriesProduct> getAll_CATELOGIES_PRODUCTS()
        {
            return new HibernateDAO<CateloriesProduct, String>().getBySQL("select * from Catelories_Product");
        }

        public List<Product> getAll_PRODUCTS()
        {
            return new HibernateDAO<Product, String>().getBySQL("select * from Products");
        }

        public List<Product> get_PRODUCTS_By_ID(int id,string pathimage)
        {
            List<Product> list = new HibernateDAO<Product, String>().getBySQL("select * from Products where id_category = " + id);
            for (int i = 0; i < list.Count;i++ )
            {
                if (new ImageService().loadImage(list[i].ID.ToString(), pathimage).Count != 0)
                {
                    list[i].PathImage = new ImageService().loadImage(list[i].ID.ToString(), pathimage)[0].Url;

                }
            }
            return list;
        }

        public List<Product> get_PRODUCTS_By_idCatelogy_Paging(int id,int pageIndex,string pathimage)
        {
              ISession session = null;
            try
            {
                session = NHibernateSessionManager.GetCurrentSession();
              var list = (List<Product>)session.GetNamedQuery("Get_ProductsPaging")
                          .SetParameter("CatelogyID", id)
                          .SetParameter("pageIndex", pageIndex)
                          .SetParameter("pageSize", 12)
                          .List<Product>();
              for (int i = 0; i < list.Count; i++)
              {
                  if (new ImageService().loadImage(list[i].ID.ToString(), pathimage).Count != 0)
                  {
                      list[i].PathImage = new ImageService().loadImage(list[i].ID.ToString(), pathimage)[0].Url;

                  }
              }  
              return list;         
            }catch(Exception e){
                return null;
            }
           

        }

        public List<int> get_List_Page_Product(int idCatelogy,int pageSize)
        {
            int o = 0;
            int count = 0;
            List<int> list = new List<int>();
            try
            {

                string connect = System.Configuration.ConfigurationManager.ConnectionStrings["VATConnectionString"].ToString();
                SqlConnection scn = new SqlConnection(connect);
                scn.Open();
                SqlCommand spcmd = new SqlCommand("Select count(ID) from Products where id_category ='"+ idCatelogy +"'", scn);
                spcmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(spcmd);
                o = (int)spcmd.ExecuteScalar();
                if ((o % pageSize) == 0)
                {
                    count = (o / pageSize);
                    for (int i = 1; i <= count; i++)
                    {
                        list.Add(i);
                    }
                    
                }
                else
                {
                    count = (o / pageSize)+1;

                    for (int i = 1; i <= count; i++)
                    {
                        list.Add(i);
                    }
                    
                }
                return list;
                scn.Close();
            }


            catch (SqlException x)
            {
                throw;
                return null;
            }


        }

        public List<Product> getTOP_PRODUCTS_NEW(string path)
        {
            //ISession hSession = NHibernateSessionManager.sessionFactory.OpenSession();
            List<Product> list = new HibernateDAO<Product, string>().getBySQL(@"select top 15 * from Products order by date_import desc");


            for (int i = 0; i < list.Count; i++)
            {
                if (new ImageService().loadImage(list[i].ID.ToString(), path + "small").Count != 0)
                {
                    list[i].PathImage = new ImageService().loadImage(list[i].ID.ToString(), path + "small")[0].Url;
                   
                }
            }

            

            return list;
        }

        public List<Product> search_Product(Product product)
        {
            HibernateDAO<Product, String> dao = new HibernateDAO<Product, String>();
            
                if (!Formater.isNull(product.Code))
                    dao.addSearchCriteria(Expression.Like("Code", product.Code, MatchMode.Anywhere).IgnoreCase());

                if (!Formater.isNull(product.Name))
                    dao.addSearchCriteria(Expression.Like("Name", product.Name, MatchMode.Anywhere).IgnoreCase());

                if (product.IdCategory != null)
                    dao.addSearchCriteria(Expression.Eq("IdCategory", product.IdCategory));

                if (product.DateImport != null)
                    dao.addSearchCriteria(Expression.Eq("DateImport", product.DateImport));

                if (product.Count != null)
                    dao.addSearchCriteria(Expression.Eq("Count", product.Count));

                if (product.Sale == 1)
                {
                    dao.addSearchCriteria(Expression.IsNotNull("Sale"));
                    dao.addSearchCriteria(Expression.Not(Expression.Eq("Sale", 0)));
                }
            
            return dao.getByCriterion();
        }
        public List<Product> search_Product_sale(string pathImage)
        {
            HibernateDAO<Product, String> dao = new HibernateDAO<Product, String>();
              dao.addSearchCriteria(Expression.Gt("Sale",0));
              List<Product> list = dao.getByCriterion();
            for (int i = 0; i < list.Count; i++)
            {
                if (new ImageService().loadImage(list[i].ID.ToString(), pathImage).Count != 0)
                {
                    list[i].PathImage = new ImageService().loadImage(list[i].ID.ToString(),pathImage)[0].Url;

                }
            }
            return list;
        }
        
        public void delete_Product(int id)
        {
            new HibernateDAO<Product, string>().ExecuteSql("delete Products where id = '" + id + "'");
        }

        public void update_Product()
        {
            
        }

        public int save_product(Product product, List<SizeProduct> listSize)
        {
            //new HibernateDAO<Product, String>().SaveOrUpdate(product);


            ISession session = null;
            ITransaction transaction = null;
            try
            {
                session = NHibernateSessionManager.GetCurrentSession();
                transaction = session.BeginTransaction();

                session.SaveOrUpdate(product);
                int idProduct = product.ID;

                IQuery query = session.CreateSQLQuery("delete Size_Product where id_product = :id").AddEntity(typeof(SizeProduct));
                query.SetInt32("id", idProduct);
                int result = query.ExecuteUpdate();

                for (int i = 0; i < listSize.Count; i++)
                {
                    listSize[i].IdProduct = idProduct;
                    session.Save(listSize[i]);
                }


                 transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                NHibernateSessionManager.CloseSession();
            }

            return product.ID;
        }

        public Product getProductByID(int id)
        {
            HibernateDAO<Product, String> dao = new HibernateDAO<Product, String>();
            dao.addSearchCriteria(Expression.Eq("ID", id));

            List<Product> temp = dao.getByCriterion();

            foreach (Product sv in temp)
            {
                return sv;
            }
            return null;
        }

        public Product getProductByID2(int id, string pathimageSmall,string pathimageLarge)
        {
            HibernateDAO<Product, String> dao = new HibernateDAO<Product, String>();
            dao.addSearchCriteria(Expression.Eq("ID", id));

            List<Product> temp = dao.getByCriterion();

            foreach (Product sv in temp)
            {
                if( new ImageService().loadImage(sv.ID.ToString(), pathimageSmall).Count != 0)
                sv.PathImage = new ImageService().loadImage(sv.ID.ToString(), pathimageSmall)[0].Url;
                if(new ImageService().loadImage(sv.ID.ToString(), pathimageLarge).Count != 0)
                sv.PathImageLarge = new ImageService().loadImage(sv.ID.ToString(), pathimageLarge)[0].UrlLager;
                return sv;
            }
            return null;
        }


        public Product getProductByCode(string code)
        {
            HibernateDAO<Product, String> dao = new HibernateDAO<Product, String>();
            dao.addSearchCriteria(Expression.Eq("Code", code));
            List<Product> temp = dao.getByCriterion();
            if (temp.Count == 1)
            {
                foreach (Product sv in temp)
                {
                    return sv;
                }

            }
            return null;
        }

        public List<SizeProduct> getListSizeByProductID(int idProduct)
        {
            HibernateDAO<SizeProduct, String> dao = new HibernateDAO<SizeProduct, String>();
            dao.addSearchCriteria(Expression.Eq("IdProduct", idProduct));
            return dao.getByCriterion();
        }

        public List<Images> getListImageByProductID(int idProduct,string pathimageSmall)
        {
            return new ImageService().loadImage(idProduct.ToString(), pathimageSmall);
        }

        public void save_Order(HeaderOrder header,List<DetailOrder> listDetail)
        {
            ISession session = null;
            ITransaction transaction = null;
            int sizeConLai = 0;
            try
            {
                session = NHibernateSessionManager.GetCurrentSession();
                transaction = session.BeginTransaction();

                session.SaveOrUpdate(header);

                IQuery query = session.CreateSQLQuery("delete Detail_Order where id_header = :id").AddEntity(typeof(DetailOrder));
                query.SetInt32("id", header.ID);
                int result = query.ExecuteUpdate();

                IQuery query2 = session.CreateSQLQuery("update Size_Product set count = :count where ID = :id_Size ").AddEntity(typeof(SizeProduct));
                IQuery query3 = session.CreateSQLQuery("delete Size_Product where ID = :id").AddEntity(typeof(SizeProduct));
                IQuery query4 = session.CreateSQLQuery("update Products set count = :count where ID = :id").AddEntity(typeof(Product));
                for (int i = 0; i < listDetail.Count; i++)
                {
                    // save bang Detail_Order
                    listDetail[i].IdHeader = header.ID;
                    session.Save(listDetail[i]);

                    if (listDetail[i].IdSize != null && !Formater.isNull(listDetail[i].Size))
                    {
                        sizeConLai = update_SizeProduct((int)listDetail[i].IdSize,(int)listDetail[i].Quantity);
                        //up date bang Size_Product
                        if(sizeConLai == 0){
                            query3.SetInt32("id", Convert.ToInt32(listDetail[i].IdSize));
                            query3.ExecuteUpdate();
                        }else{
                            query2.SetInt32("count", sizeConLai);                 
                            query2.SetInt32("id_Size", Convert.ToInt32(listDetail[i].IdSize));
                            query2.ExecuteUpdate();
                        }
                    }

                    // update so luong bang Products
                    query4.SetInt32("count", update_CountProduct((int)listDetail[i].IdProduct, (int)listDetail[i].Quantity));
                    query4.SetInt32("id", Convert.ToInt32(listDetail[i].IdProduct));
                    query4.ExecuteUpdate();
                    
                }

            transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                NHibernateSessionManager.CloseSession();
            }

        }

        public int update_SizeProduct(int id_Size, int newCount)
        {

            HibernateDAO<SizeProduct, String> dao = new HibernateDAO<SizeProduct, String>();
            dao.addSearchCriteria(Expression.Eq("ID", id_Size));

            List<SizeProduct> temp = dao.getByCriterion();

            foreach (SizeProduct sv in temp)
            {
              return  ((int)sv.Count - newCount);
            }
            return 0;
        }

        public int update_CountProduct(int idProdcut,int newCount)
        {

            HibernateDAO<Product, String> dao = new HibernateDAO<Product, String>();
            dao.addSearchCriteria(Expression.Eq("ID", idProdcut));

            List<Product> temp = dao.getByCriterion();

            foreach (Product sv in temp)
            {
                return ((int)sv.Count - newCount);
            }
            return 0;
        }

        public List<Product> getAllStore()
        {
          //  return new HibernateDAO<Product, String>().getBySQL("select * from Products where count != 0");
            List<Product> lstTemp = null;
            ITransaction tran = null;
            ISession  hsession = null;
            try
            {
               hsession = NHibernateSessionManager.GetCurrentSession();

            
                lstTemp = (List<Product>)hsession.CreateSQLQuery(@"select {p.*} from Products {p} where count != 0").AddEntity("p",typeof(Product)).List<Product>();
                
            }
            catch (Exception e) { Console.WriteLine(e.Message); }
            //finally { hsession.Flush(); hsession.Close(); }

            return lstTemp;
        }


        public DataTable getBKSP()
        {
            DataSet dataset = new DataSet();
            try
            {
               
                string connect = System.Configuration.ConfigurationManager.ConnectionStrings["VATConnectionString"].ToString();
                SqlConnection scn = new SqlConnection(connect);
                scn.Open();
                SqlCommand spcmd = new SqlCommand("TK_HangTon", scn);
                spcmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(spcmd);

                da.Fill(dataset);
                scn.Close();
            }
            
                    
                    catch (SqlException x)
                    {
                        throw;
                    }

            return dataset.Tables[0]; 
        }


    }
}
